/**DBUtils Encapsulation for common operation and pool management
 * the module encapsulate DBUtnils
 * by fuzhou huilin corp.ltd @author lms 2016.07.07
 * 
 **************************************************
 * update by @author lms 2020.10.11 
 * 1、代码优化，去掉getConnection等带参数接口；
 * 2、增加SQLServer连接支持；
 * 3、增加获取数据库类型接口；
 */
package ms.core.db.mysql;

import ms.core.common.AppException;
import ms.core.common.Page;
import ms.core.common.consts.SysErr;
import ms.core.db.mysql.DbConfTool.DbConf;
import ms.core.db.mysql.DbConfTool.DbType;
import ms.core.tool.Log4j2Tool;
import ms.core.tool.StrTool;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DbUtilsDrv implements IDbDrv{
	private static final String configFile = "/config/c3p0.xml";
  	private static final String CONF_DEFAULT = "default";
  	private static Map<String, ComboPooledDataSource> pools = null;
	private static ThreadLocal<Map<String, Connection>> context = null;
  	// 当前实例相关对象
	private Connection conn = null;				// 当前连接
  	private String currentConfigName = null;	// 当前数据库配置节点名
  	private DbConf dbConf = null;				// 当前数据库配置信息

  	static {
  		init();
    }
  	
  	private synchronized static void init() {
      	pools = new HashMap<String, ComboPooledDataSource>();
    	context = new ThreadLocal<Map<String, Connection>>();
  	}

  	private synchronized void registerDataSource(String configName) {
  		if (pools.get(configName)!=null)
  			return;

  		dbConf = DbConfTool.loadConf(CONF_DEFAULT.equalsIgnoreCase(configName) ? null : configName, configFile);
  		if (dbConf==null)
  			throw new AppException(SysErr.DB_CONFIG_ERR,"未配置"+configName+"连接配置参数.");

  		try {
  			ComboPooledDataSource source = new ComboPooledDataSource();
  			source.setJdbcUrl(dbConf.getJdbcUrl());
  			source.setDriverClass(dbConf.getDriverClass());
  			source.setUser(dbConf.getUser());
  			source.setPassword(dbConf.getPassword());
  			source.setInitialPoolSize(dbConf.getInitialPoolSize());
  			source.setMinPoolSize(dbConf.getMinPoolSize());
  			source.setMaxPoolSize(dbConf.getMaxPoolSize());
  			source.setMaxIdleTime(dbConf.getMaxIdleTime());
  			source.setMaxStatements(dbConf.getMaxStatements());
  			source.setMaxStatementsPerConnection(dbConf.getMaxStatementsPerConnection());

  			pools.put(configName, source);
  		} catch(Exception e) {
  			throw new AppException(SysErr.DB_CONNECT_ERR, e);
  		}
  	}

	private boolean openConnection(String configName) {
    	//从当前线程取出连接集合
    	Map<String, Connection> conns = context.get();
    	if (conns==null){
    		conns = new HashMap<String, Connection>();
    		context.set(conns);
    	}

    	conn = conns.get(configName);
    	if (conn!=null) return true;

    	ComboPooledDataSource ds = pools.get(configName);
    	try {
			conn = ds.getConnection();
			conns.put(configName, conn);
	    	return true;
		} catch (Exception e) {
			Log4j2Tool.error(this.getClass(), "openConnection", e);
			throw new AppException(SysErr.DB_CONNECT_ERR);
		}
	}

	@Override
	public boolean closeConnection(){
	   	//从当前线程取出连接集合
    	Map<String, Connection> conns = context.get();
    	if (conns==null) return false;

    	closeConnection(conns.get(this.currentConfigName));
    	conns.remove(this.currentConfigName);
    	if (conns.size()==0) {
    		context.remove();
    		conns = null;
    	}
    	return true;
	}

	@Override
	public Connection getConnection(){
		openConnection(currentConfigName);
		Map<String, Connection> conns = context.get();
		return conns.get(currentConfigName);
	}

	@Override
	public String getDatabaseName() {
		try {
			Connection conn = getConnection();
			if (conn!=null) return conn.getCatalog();

			return null;
		}catch(Exception e) {
			Log4j2Tool.error(this.getClass(), "getDatabaseName", e);
			throw new AppException(SysErr.DB_CONNECT_ERR);
		}
	}

	/**
	 * 获取数据库类型
	 * @return
	 */
	@Override
	public DbType getDbType() {
		DbType ret = DbType.dbUnknown;
		try {
			String productName = conn.getMetaData().getDatabaseProductName().toLowerCase();
        	if (productName.contains("mysql"))
        		ret = DbType.dbMySql;
        	else if (productName.contains("microsoft sql server"))
        		ret = DbType.dbMsSql;
		} catch(Exception e) {
			Log4j2Tool.error(this.getClass(), "getDatabaseName", e);
			throw new AppException(SysErr.DB_CONFIG_ERR, "getDbType");
		}
		
		return ret;
	}

	private static synchronized void closeConnection(Connection conn){
		if (conn==null) return;
		
		try {
			DbUtils.closeQuietly(conn);
			conn = null;
		} catch (Exception e) {
			Log4j2Tool.error(DbUtilsDrv.class, "closeConnection", e);
		}
	}

	/**关闭当前线程的所有连接
	 * to close current session connection
	 * @return
	 */
	public static synchronized boolean closeSessionConnection(){
    	//从当前线程取出连接集合
    	Map<String, Connection> conns = context.get();
    	if (conns==null) return false;

    	for(Map.Entry<String, Connection> item: conns.entrySet()){
    		closeConnection(item.getValue());
    	}
    	conns = null;
    	context.remove();
    	return true;
	}

	/**
	 * 启动事务
	 * @return
	 */
	public static synchronized void startTransaction() {
		DbUtilsDrv db = new DbUtilsDrv();
		db.startTran();
	}

	/**
	 * 启动事务
	 * @return
	 */
	public static synchronized void startTransaction(String configName) {
		DbUtilsDrv db = new DbUtilsDrv(configName);
		db.startTran();
	}

	/**
	 * 回滚事务
	 * @return
	 */
	public static synchronized void rollTransaction() {
		DbUtilsDrv db = new DbUtilsDrv();
		db.rollTran();
	}

	/**
	 * 回滚事务
	 * @return
	 */
	public static synchronized void rollTransaction(String configName) {
		DbUtilsDrv db = new DbUtilsDrv(configName);
		db.rollTran();
	}

	/**
	 * 提交事务
	 * @return
	 */
	public static synchronized void commitTransaction() {
		DbUtilsDrv db = new DbUtilsDrv();
		db.commitTran();
	}

	/**
	 * 提交事务
	 * @return
	 */
	public static void commitTransaction(String configName) {
		DbUtilsDrv db = new DbUtilsDrv(configName);
		db.commitTran();
	}

	public DbUtilsDrv(){
		currentConfigName = CONF_DEFAULT;
		registerDataSource(currentConfigName);
    	openConnection(currentConfigName);
    }

    public DbUtilsDrv(String configName){
    	currentConfigName = configName;
		registerDataSource(currentConfigName);
    	openConnection(configName);
    }

	@Override
	public void startTran() {
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			Log4j2Tool.error(DbUtilsDrv.class, "startTran", e);
			throw new AppException(SysErr.DB_STARTTRAN_ERR);
		}
	}

	@Override
	public void rollTran() {
		try {
			conn.rollback();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			Log4j2Tool.error(DbUtilsDrv.class, "rollTran", e);
			throw new AppException(SysErr.DB_ROLLTRAN_ERR);
		}
	}

	@Override
	public void commitTran() {
		try {
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			Log4j2Tool.error(DbUtilsDrv.class, "commitTran", e);
			throw new AppException(SysErr.DB_COMMITTRAN_ERR);
		}
	}

	@Override
	public int executeSql(String sql) {
    	try{
			QueryRunner qr = new QueryRunner();
			int ret = qr.update(conn, sql);
			qr = null;
			return ret;
		} catch (Exception e) {
			Log4j2Tool.error(DbUtilsDrv.class, "executeSql,sql=>"+sql, e);
			throw new AppException(SysErr.DB_SQL_ERR);
		}
	}

	@Override
	public int executeSql(String sql, Object[] params) {
		try {
			QueryRunner qr = new QueryRunner();
			int ret = qr.update(conn, sql, params);
			return ret;
		} catch (Exception e) {
			Log4j2Tool.error(DbUtilsDrv.class, "executeSql,sql=>"+sql, e);
			throw new AppException(SysErr.DB_SQL_ERR);
		}
	}

	@Override
	public int executeSql(String sql, List<Object> params) {
		if (params==null) params = new ArrayList<Object>();
		return executeSql(sql, params.toArray());
	}

	/**
	 * 批量执行SQL
	 * @param sql 执行脚本
	 * @param 参数列表
	 */
	@Override
	public int batchExecuteSql(String sql, List<Object[]> params) {
		try {
			Object[][] p = new Object[params.size()][];
			QueryRunner qr = new QueryRunner();
			for(int i=0; i<params.size(); i++) {
				p[i] = params.get(i);
			}
			
			int ret = 0;
			int rets[] = qr.batch(conn, sql, p);

			for(int i=0; i<rets.length; i++) 
				ret += rets[i];

			return ret;
		} catch (Exception e) {
			Log4j2Tool.error(DbUtilsDrv.class, "batchExecuteSql", e);
			throw new AppException(SysErr.DB_SQL_ERR);
		}		
	}
	
	@Override
	public Object queryFieldValue(String sql, Class<?> cls, int col) {
		Object ret = null;
		QueryRunner qr = null;
		try {
			qr = new QueryRunner();
			ret = qr.query(conn, sql, new ScalarHandler<Class<?>>(col));    //column start with 1
		} catch (SQLException e) {
			Log4j2Tool.error(DbUtilsDrv.class, "queryFieldValue, sql=>"+sql, e);
			throw new AppException(SysErr.DB_SQL_ERR);
		}
		qr = null;
		return ret;
	}

	@Override
	public Object queryFieldValue(String sql, Class<?> cls, Object[] params, int col) {
		Object ret = null;
		QueryRunner qr = null;
		try {
			qr = new QueryRunner();
			ret = qr.query(conn, sql, new ScalarHandler<Class<?>>(col), params);    //to get column 1 value
		} catch (SQLException e) {
			Log4j2Tool.error(DbUtilsDrv.class, "queryFieldValue", e);
			throw new AppException(SysErr.DB_SQL_ERR);
		}
		qr = null;
		return ret;
	}

	@Override
	public Object queryFieldValue(String sql, Class<?> cls, List<Object> params, int col) {
		if (params==null) params = new ArrayList<Object>();
		return queryFieldValue(sql, cls, params.toArray(), col);
	}

	@Override
	@SuppressWarnings("unchecked")
	public <T> T queryForBean(String sql, Class<T> cls) {
		QueryRunner qr = null;

		try {
			qr = new QueryRunner();
			if (cls.getSimpleName().equals("Map"))
				return (T) qr.query(conn, sql, new MapHandler());
			else
				return qr.query(conn, sql, new BeanHandler<T>(cls));
		} catch (SQLException e) {
			Log4j2Tool.error(DbUtilsDrv.class, "queryForBean", e);
			throw new AppException(SysErr.DB_SQL_ERR);
		}
	}

	@Override
	@SuppressWarnings("unchecked")
	public <T> T queryForBean(String sql, Class<T> cls, Object[] params) {
		QueryRunner qr = null;

		try {
			qr = new QueryRunner();
			if (cls.getSimpleName().equals("Map"))
				return (T) qr.query(conn, sql, new MapHandler(), params);
			else
				return qr.query(conn, sql, new BeanHandler<T>(cls), params);
		} catch (SQLException e) {
			Log4j2Tool.error(DbUtilsDrv.class, "queryForBean", e);
			throw new AppException(SysErr.DB_SQL_ERR);
		}
	}

	@Override
	public <T> T queryForBean(String sql, Class<T> cls, List<Object> params) {
		if (params==null) params = new ArrayList<Object>();
		return queryForBean(sql, cls, params.toArray());
	}

	@Override
	@SuppressWarnings("unchecked")
	public <T> List <T> queryForBeans(String sql, Class<T> cls) {
		QueryRunner qr = null;

		try{
			qr = new QueryRunner();
			if (cls.getSimpleName().equals("Map"))
				return (List<T>) qr.query(conn, sql, new MapListHandler());
			else
				return qr.query(conn,sql,new BeanListHandler<T>(cls));
    	} catch(SQLException e){
			Log4j2Tool.error(DbUtilsDrv.class, "queryForBeans", e);
    		throw new AppException(SysErr.DB_SQL_ERR);
    	}
	}

	@Override
	@SuppressWarnings("unchecked")
	public <T> List<T> queryForBeans(String sql, Class<T> cls, Object[] params) {
		QueryRunner qr = null;
		try {
			qr = new QueryRunner();
			if (cls.getSimpleName().equals("Map"))
				return (List<T>) qr.query(conn, sql, new MapListHandler(), params);
			else
				return qr.query(conn, sql, new BeanListHandler<T>(cls), params);
		} catch (SQLException e) {
			Log4j2Tool.error(DbUtilsDrv.class, "queryForBeans", e);
			throw new AppException(SysErr.DB_SQL_ERR);
		}
	}

	@Override
	public <T> List<T> queryForBeans(String sql, Class<T> cls, List<Object> params) {
		if (params==null) params = new ArrayList<Object>();
		return queryForBeans(sql, cls, params.toArray());
	}

	private int calculatePages(long rows, int pagesize) {
		return Math.round((rows+pagesize-1)/pagesize);
	}

	/**
	 * 说明：count函数，不同类型数据库映射到java类型也不同。
	 */
	@Override
	public <T>Page<T> queryForPage(QueryParam qp, Class<T> cls) {
		Page<T> ret = new Page<>();
		ret.setPageInfo(qp.getPage(), qp.getPagesize(), 0, 0, null);

		if (!StrTool.isBlank(qp.getCntSql())) {
			Object tmp = (Object) queryFieldValue(qp.getCntSql(), Object.class, qp.getParams(), 1);
			if (tmp==null) return ret;
			
			Integer rows = Integer.valueOf(tmp.toString());
			ret.setRows(rows);
			int pages = 0;
			if (qp.getPage()>0 && qp.getPagesize()>0) {
				pages = calculatePages(rows, qp.getPagesize());
				ret.setPages(pages);
				if (pages<qp.getPage()) {
					return ret;
				}
			}
		}

		List<T> list = this.queryForBeans(qp.getQrySql(getDbType()), cls, qp.getParams());
		if (list==null || list.size()==0) {
			return ret;
		}

		ret.setData(list);
		return ret;
	}

	@Override
	public boolean tableExists(String table) {
		boolean ret = false;

    	try{
    		String sql = String.format("select * from %s where 1<0",table);
    		QueryRunner qr = new QueryRunner();
    		qr.query(conn,sql,new ArrayListHandler());
    		ret = true;
    	} catch(SQLException e){
			Log4j2Tool.error(DbUtilsDrv.class, "tableExists:", e);
    		throw new AppException(SysErr.DB_TABLE_NOT_FOUND);
    	}
    	return ret;
	}

	/**
	 * 说明：count函数，不同数据库映射到java类型也不同。
	 * SQLServer=>Integer
	 * MySql=>Long
	 */
	@Override
	public int queryRecordCount(String table, Map<String, Object> cdt) {
		List<Object> params = new ArrayList<Object>();
		String sql = "select count(*) from "+table+" where 1=1";

		if (cdt != null) {
			for (Map.Entry<String, Object> c : cdt.entrySet()) {
				sql = sql + " and " + c.getKey() + "=?";
				params.add(c.getValue());
			}
		}
		Object tmp = (Long) queryFieldValue(sql,Object.class,params,1);
		if (tmp==null) return -1;

		return Integer.valueOf(tmp.toString());
	}

	@Override
	public int insertRecord(String table, Map<String, Object> values) {
		if (values==null || values.size()==0)
			throw new AppException(SysErr.SYS_PARAM_ERR, "insert record values can't be null or empty");

		String tmp="";
		List<Object> params = new ArrayList<Object>();
		String sql = "insert into "+table+"(";

		for (Map.Entry<String, Object> v: values.entrySet()){
			sql = sql+v.getKey()+",";
			tmp = tmp+"?,";
			params.add(v.getValue());
		}
		sql = StrTool.deleteCharAt(sql, sql.length()-1)+") values(";
		tmp = StrTool.deleteCharAt(tmp, tmp.length()-1)+");";
		sql = sql+tmp;
		return executeSql(sql,params);
	}

	/**
	 * 批量写入记录
	 * @param table 写入的表名称
	 * @param values 写入记录值
	 */
	@Override
	public int insertRecord(String table, String operator, List<Map<String, Object>> values) {
		if (StrTool.isBlank(operator)) {
			throw new AppException(SysErr.SYS_PARAM_ERR,"operator parameter can't be empty,it can be 'insert' or 'replace'.");
		}
		if (values==null || values.size()==0) {
			throw new AppException(SysErr.SYS_PARAM_ERR,"insert multiple records can't be null or without any records");
		}

		List<Object[]> params = new ArrayList<>();

		String tmp="";
		String sql = operator+" into "+table+"(";
		for(int i=0; i<values.size(); i++) {
			Map<String, Object> v = values.get(i);
			Object[] p = new Object[v.size()];
			
			int col = 0;
			for(Map.Entry<String, Object> entry: v.entrySet()) {
				if (i==0) {
					sql = sql+entry.getKey()+",";
					tmp += "?,"; 
				}
				p[col++] = entry.getValue();
			}
			params.add(p);
		}
		sql = StrTool.deleteCharAt(sql, sql.length()-1)+") values("+StrTool.deleteCharAt(tmp, tmp.length()-1)+")";
		return this.batchExecuteSql(sql, params);
	}
	
	@Override
	public int modifyRecord(String table, Map<String, Object> values, Map<String, Object> cdt) {
		if (values==null)
			throw new AppException(SysErr.SYS_PARAM_ERR,"modify record values can't be null");

		List<Object> params = new ArrayList<Object>();
		String sql="update "+table+" set ";

		for(Map.Entry<String, Object> v: values.entrySet()){
			sql = sql+v.getKey()+"=?,";
			params.add(v.getValue());
		}
		sql = StrTool.deleteCharAt(sql, sql.length()-1);

		sql = sql+" where 1=1";
		if (cdt != null) {
			for (Map.Entry<String, Object> c : cdt.entrySet()) {
				sql = sql + " and " + c.getKey() + "=?";
				params.add(c.getValue());
			}
		}

		return executeSql(sql,params);
	}

	@Override
	public int deleteRecord(String table, Map<String, Object> cdt) {
		List<Object> params = new ArrayList<Object>();
		String sql = "delete from "+table+" where 1=1";

		if (cdt!=null) {
			for(Map.Entry<String, Object> c: cdt.entrySet()){
				sql = sql+" and "+c.getKey()+"=?";
				params.add(c.getValue());
			}
		}
		return executeSql(sql,params);
	}

	@Override
	public int deleteRecord(String table, long id) {
		String sql = String.format("delete from %s where id=%d", table, id);
		return executeSql(sql);
	}

	@SuppressWarnings("unchecked")
	@Override
	public <T> T getRecord(String table, long id, Class<T> cls) {
		QueryRunner qr = null;
		T ret = null;
		
		String sql = "select * from "+table+" where id="+id;
		try {
			qr = new QueryRunner();
			if (cls.getSimpleName().equals("Map"))
				ret = (T) qr.query(conn, sql, new MapHandler());
			else
				ret = qr.query(conn, sql, new BeanHandler<T>(cls));
		} catch (SQLException e) {
			throw new AppException(SysErr.DB_SQL_ERR, e.getMessage());
		}

		qr = null;
		return ret;
	}
	

	@Override
	public <T> T getRecord(String table, Map<String, Object> cdt, Class<T> cls) {
		List<Object> params = new ArrayList<Object>();
		
		String sql = "select * from "+table+" where 1=1";
		if (dbConf.getDbType()==DbType.dbMsSql)
			sql = "select top 1 * from "+table+" where 1=1";

		if (cdt!=null) {
			for(Map.Entry<String, Object> c: cdt.entrySet()){
				sql = sql+" and "+c.getKey()+"=?";
				params.add(c.getValue());
			}
		}
		
		if (dbConf.getDbType()==DbType.dbMySql)
			sql += " limit 1";

		return queryForBean(sql, cls, params);
	}
}
